﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_BGM_ProjectOperationReport : IDAL_BGM_ProjectOperationReport
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public D_BGM_ProjectOperationReport()
        {

        }

        public IList<M_BGM_ProjectOperationReport> GetFull(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_proj_oper_exp_seq, Nullable<int> rpt_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);

                strSQL = "SELECT rpt.bgm_proj_oper_rpt_seq, rpt.bgm_proj_oper_exp_seq"//, rpt.bgm_trans_from_seq, rpt.bgm_trans_to_seq"
                    + ", rpt.icm_dn_item_seq, rpt.money_req_dtls_seq"
                    + ", rpt.return_dtls_seq, rpt.petition_dtls_seq, rpt.pay_seq"
                    + ", rpt.used_amount, rpt.cal_amount, rpt.rpt_type, rpt.receive_seq"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " and opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and rpt.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (rpt_type != null ? " and rpt.rpt_type = @rpt_type" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_rpt_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_rpt_seq"]);
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        //info.bgm_trans_from_seq = cn.ConvertNullInt(drc[i]["bgm_trans_from_seq"]);
                        //info.bgm_trans_to_seq = cn.ConvertNullInt(drc[i]["bgm_trans_to_seq"]);
                        info.icm_dn_item_seq = cn.ConvertNullInt(drc[i]["icm_dn_item_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.return_dtls_seq = cn.ConvertNullInt(drc[i]["return_dtls_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.receive_seq = cn.ConvertNullInt(drc[i]["receive_seq"]);
                        info.used_amount = cn.ConvertNullDouble(drc[i]["used_amount"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        info.rpt_type = cn.ConvertNullInt(drc[i]["rpt_type"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationReport> GetFull(int? bgm_proj_oper_rpt_seq, int? bgm_proj_oper_exp_seq
            , int? icm_ppr_item_seq, int? icm_pr_item_seq, int? icm_po_install_item_seq, int? icm_dn_item_seq
            , int? money_req_dtls_seq, int? return_dtls_seq, int? petition_dtls_seq, int? pay_seq, int? receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_rpt_seq", bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@icm_ppr_item_seq", icm_ppr_item_seq);
                dbManager.AddParameters(4, "@icm_pr_item_seq", icm_pr_item_seq);
                dbManager.AddParameters(5, "@icm_po_install_item_seq", icm_po_install_item_seq);
                dbManager.AddParameters(6, "@icm_dn_item_seq", icm_dn_item_seq);
                dbManager.AddParameters(7, "@money_req_dtls_seq", money_req_dtls_seq);
                dbManager.AddParameters(8, "@petition_dtls_seq", petition_dtls_seq);
                dbManager.AddParameters(9, "@pay_seq", pay_seq);
                dbManager.AddParameters(10, "@receive_seq", receive_seq);

                strSQL = "SELECT rpt.bgm_proj_oper_rpt_seq, rpt.bgm_proj_oper_exp_seq"//, rpt.bgm_trans_from_seq, rpt.bgm_trans_to_seq"
                    + ", rpt.icm_dn_item_seq, rpt.money_req_dtls_seq"
                    + ", rpt.return_dtls_seq, rpt.petition_dtls_seq, rpt.pay_seq, rpt.receive_seq"
                    + ", rpt.used_amount, rpt.cal_amount, rpt.rpt_type"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.record_status not like @delete_status"
                    + (bgm_proj_oper_rpt_seq != null ? " and rpt.bgm_proj_oper_rpt_seq = @bgm_proj_oper_rpt_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and rpt.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (icm_ppr_item_seq != null ? " and rpt.icm_ppr_item_seq = @icm_ppr_item_seq" : "")
                    + (icm_pr_item_seq != null ? " and rpt.icm_pr_item_seq = @icm_pr_item_seq" : "")
                    + (icm_po_install_item_seq != null ? " and rpt.icm_po_install_item_seq = @icm_po_install_item_seq" : "")
                    + (icm_dn_item_seq != null ? " and rpt.icm_dn_item_seq = @icm_dn_item_seq" : "")
                    + (money_req_dtls_seq != null ? " and rpt.money_req_dtls_seq = @money_req_dtls_seq" : "")
                    + (petition_dtls_seq != null ? " and rpt.petition_dtls_seq = @petition_dtls_seq" : "")
                    + (pay_seq != null ? " and rpt.pay_seq = @pay_seq" : "")
                    + (receive_seq != null ? " and rpt.receive_seq = @receive_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_rpt_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_rpt_seq"]);
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        //info.bgm_trans_from_seq = cn.ConvertNullInt(drc[i]["bgm_trans_from_seq"]);
                        //info.bgm_trans_to_seq = cn.ConvertNullInt(drc[i]["bgm_trans_to_seq"]);
                        info.icm_dn_item_seq = cn.ConvertNullInt(drc[i]["icm_dn_item_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.return_dtls_seq = cn.ConvertNullInt(drc[i]["return_dtls_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.receive_seq = cn.ConvertNullInt(drc[i]["receive_seq"]);
                        info.used_amount = cn.ConvertNullDouble(drc[i]["used_amount"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        info.rpt_type = cn.ConvertNullInt(drc[i]["rpt_type"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationReport> GetList(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_proj_oper_exp_seq, Nullable<int> rpt_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);

                strSQL = "SELECT rpt.bgm_proj_oper_exp_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " and opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and rpt.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (rpt_type != null ? " and rpt.rpt_type = @rpt_type" : "")
                    + " ORDER BY rpt.bgm_proj_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationReport> GetCalculateList(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_proj_oper_exp_seq, Nullable<int> rpt_type
            , Nullable<int> bgm_trans_from_seq, Nullable<int> bgm_trans_to_seq
            , Nullable<int> bgm_booking_seq, Nullable<int> icm_ppr_item_seq
            , Nullable<int> icm_pr_item_seq, Nullable<int> icm_po_item_seq
            , Nullable<int> icm_dn_item_seq, Nullable<int> money_req_dtls_seq
            , Nullable<int> return_dtls_seq, Nullable<int> petition_dtls_seq
            , Nullable<int> pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);
                dbManager.AddParameters(4, "@bgm_trans_from_seq", "");
                dbManager.AddParameters(5, "@bgm_trans_to_seq", "");
                dbManager.AddParameters(6, "@bgm_booking_seq", bgm_booking_seq);
                dbManager.AddParameters(7, "@icm_ppr_item_seq", icm_ppr_item_seq);
                dbManager.AddParameters(8, "@icm_pr_item_seq", icm_pr_item_seq);
                dbManager.AddParameters(9, "@icm_po_item_seq", icm_po_item_seq);
                dbManager.AddParameters(10, "@icm_dn_item_seq", icm_dn_item_seq);
                dbManager.AddParameters(11, "@money_req_dtls_seq", money_req_dtls_seq);
                dbManager.AddParameters(12, "@return_dtls_seq", return_dtls_seq);
                dbManager.AddParameters(13, "@petition_dtls_seq", petition_dtls_seq);
                dbManager.AddParameters(14, "@pay_seq", pay_seq);

                strSQL = "SELECT rpt.bgm_proj_oper_exp_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as cal_amount"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " and rpt.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and rpt.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq" : "")
                    + (rpt_type != null ? " and rpt_type = @rpt_type" : "")
                    //+ (bgm_trans_from_seq != null ? " and coalesce(bgm_trans_from_seq, -1) not like @bgm_trans_from_seq" : "")
                    //+ (bgm_trans_to_seq != null ? " and coalesce(bgm_trans_to_seq, -1) not like @bgm_trans_to_seq" : "")
                    + (bgm_booking_seq != null ? " and coalesce(bgm_booking_seq, -1) not like @bgm_booking_seq" : "")
                    + (icm_ppr_item_seq != null ? " and coalesce(icm_ppr_item_seq, -1) not like @icm_ppr_item_seq" : "")
                    + (icm_pr_item_seq != null ? " and coalesce(icm_pr_item_seq, -1) not like @icm_pr_item_seq" : "")
                    + (icm_po_item_seq != null ? " and coalesce(icm_po_item_seq, -1) not like @icm_po_item_seq" : "")
                    + (icm_dn_item_seq != null ? " and coalesce(icm_dn_item_seq, -1) not like @icm_dn_item_seq" : "")
                    + (money_req_dtls_seq != null ? " and coalesce(money_req_dtls_seq, -1) not like @money_req_dtls_seq" : "")
                    + (return_dtls_seq != null ? " and coalesce(return_dtls_seq, -1) not like @return_dtls_seq" : "")
                    + (petition_dtls_seq != null ? " and coalesce(petition_dtls_seq, -1) not like @petition_dtls_seq" : "")
                    + (pay_seq != null ? " and coalesce(pay_seq, -1) not like @pay_seq" : "")
                    + " ORDER BY bgm_proj_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationReport> GetPreviousRpt(int seq, int rpt_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationReport> infoList = new List<M_BGM_ProjectOperationReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", seq);

                int nPrevRptType = 0;
                switch (rpt_type)
                {
                    case 7 :
                        nPrevRptType = 6;
                        strSQL = "select rpt.*"
                            + " from bgm_project_operation_report rpt"
                            + " left join fim_money_request_dtls mrd on rpt.money_req_dtls_seq = mrd.money_req_dtls_seq"
                            + " left join fim_payments pay on mrd.money_req_seq = pay.money_req_seq"

                            + " where rpt.record_status not like @delete_status"
                            + " and pay.pay_seq = @seq"
                            + " and rpt.rpt_type = @rpt_type";
                        break;
                    case 6:
                        nPrevRptType = 4;
                        strSQL = "select rpt.bgm_proj_oper_rpt_seq, rpt.bgm_proj_oper_exp_seq, rpt.icm_ppr_item_seq"
                            + ", rpt.icm_pr_item_seq, rpt.icm_dn_item_seq, rpt.money_req_dtls_seq, rpt.return_dtls_seq"
                            + ", rpt.petition_dtls_seq, rpt.pay_seq, rpt.cal_amount, rpt.rpt_type, rpt.receive_seq"
                            + ", dni.NetAmount as used_amount"
                            + " from fim_money_requests mr"
                            + " left join IcmDeliveryNoteItem dni on mr.icm_dn_seq = dni.DnId"
                            + " left join IcmPurchaseOrderInstallmentItem poii on dni.PoInstallItemId = poii.PoInstallItemId"
                            + " left join IcmBidderQuotation quo on poii.BidderQuoId = quo.BidderQuoId"
                            + " left join bgm_project_operation_report rpt on quo.PrItemId = rpt.icm_pr_item_seq"

                            + " where mr.money_req_seq = @seq"
                            + " and rpt.record_status not like @delete_status";
                        break;
                    default :
                        nPrevRptType = 6;
                        strSQL = "";
                        break;
                }

                dbManager.AddParameters(2, "@rpt_type", nPrevRptType);

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
                        info.bgm_proj_oper_rpt_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_rpt_seq"]);
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        //info.bgm_trans_from_seq = cn.ConvertNullInt(drc[i]["bgm_trans_from_seq"]);
                        //info.bgm_trans_to_seq = cn.ConvertNullInt(drc[i]["bgm_trans_to_seq"]);
                        info.icm_ppr_item_seq = cn.ConvertNullInt(drc[i]["icm_ppr_item_seq"]);
                        info.icm_pr_item_seq = cn.ConvertNullInt(drc[i]["icm_pr_item_seq"]);
                        info.icm_dn_item_seq = cn.ConvertNullInt(drc[i]["icm_dn_item_seq"]);
                        info.money_req_dtls_seq = cn.ConvertNullInt(drc[i]["money_req_dtls_seq"]);
                        info.return_dtls_seq = cn.ConvertNullInt(drc[i]["return_dtls_seq"]);
                        info.petition_dtls_seq = cn.ConvertNullInt(drc[i]["petition_dtls_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.used_amount = cn.ConvertNullDouble(drc[i]["used_amount"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        info.rpt_type = cn.ConvertNullInt(drc[i]["rpt_type"]);
                        info.receive_seq = cn.ConvertNullInt(drc[0]["receive_seq"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectOperationReport GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectOperationReport info = new M_BGM_ProjectOperationReport();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT rpt.bgm_proj_oper_rpt_seq, rpt.bgm_proj_oper_exp_seq"
                    + ", rpt.icm_dn_item_seq, rpt.money_req_dtls_seq"
                    + ", rpt.return_dtls_seq, rpt.petition_dtls_seq, rpt.pay_seq"
                    + ", rpt.used_amount, rpt.cal_amount, rpt.rpt_type, rpt.receive_seq"

                    + " FROM bgm_project_operation_report rpt"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON rpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " WHERE rpt.bgm_proj_oper_rpt_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_oper_rpt_seq = Convert.ToInt32(drc[0]["bgm_proj_oper_rpt_seq"]);
                    info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_exp_seq"]);
                    //info.bgm_trans_from_seq = cn.ConvertNullInt(drc[0]["bgm_trans_from_seq"]);
                    //info.bgm_trans_to_seq = cn.ConvertNullInt(drc[0]["bgm_trans_to_seq"]);
                    info.icm_dn_item_seq = cn.ConvertNullInt(drc[0]["icm_dn_item_seq"]);
                    info.money_req_dtls_seq = cn.ConvertNullInt(drc[0]["money_req_dtls_seq"]);
                    info.return_dtls_seq = cn.ConvertNullInt(drc[0]["return_dtls_seq"]);
                    info.petition_dtls_seq = cn.ConvertNullInt(drc[0]["petition_dtls_seq"]);
                    info.pay_seq = cn.ConvertNullInt(drc[0]["pay_seq"]);
                    info.used_amount = cn.ConvertNullDouble(drc[0]["used_amount"]);
                    info.cal_amount = cn.ConvertNullDouble(drc[0]["cal_amount"]);
                    info.rpt_type = cn.ConvertNullInt(drc[0]["rpt_type"]);
                    info.receive_seq = cn.ConvertNullInt(drc[0]["receive_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_BGM_ProjectOperationReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@bgm_proj_oper_rpt_seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@descr", info.descr);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@bgm_trans_from_seq", "");
                dbManager.AddParameters(4, "@bgm_trans_to_seq", "");
                dbManager.AddParameters(5, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(6, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(7, "@petition_dtls_seq", info.petition_dtls_seq);
                dbManager.AddParameters(8, "@pay_seq", info.pay_seq);

                dbManager.AddParameters(9, "@used_amount", info.used_amount);
                dbManager.AddParameters(10, "@cal_amount", info.cal_amount);
                dbManager.AddParameters(11, "@rpt_type", info.rpt_type);
                
                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);
                dbManager.AddParameters(15, "@receive_seq", info.receive_seq);

                strSQL = "select count(*) as row_count"
                    + " from bgm_project_operation_report"
                    + " where bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq"
                    + " and used_amount = @used_amount"
                    + " and cal_amount = @cal_amount"
                    + " and rpt_type = @rpt_type"
                    + " and DATEDIFF(SECOND, created_when, GETDATE()) < 30";
                var exists = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (exists.Rows.Count > 0 && (cn.ConvertNullInt(exists.Rows[0][0]) ?? 0) > 0)
                {
                    return;
                }

                strSQL = "INSERT INTO bgm_project_operation_report"
                    + "(descr, bgm_proj_oper_exp_seq"//, bgm_trans_from_seq, bgm_trans_to_seq"
                        + ", money_req_dtls_seq, return_dtls_seq, petition_dtls_seq, pay_seq, receive_seq"
                        + ", used_amount, cal_amount, rpt_type"
                        + ", record_status, created_by, created_when)"
                    + " VALUES (@descr, @bgm_proj_oper_exp_seq"//, @bgm_trans_from_seq, @bgm_trans_to_seq"
                        + ", @money_req_dtls_seq, @return_dtls_seq, @petition_dtls_seq, @pay_seq, @receive_seq"
                        + ", @used_amount, @cal_amount, @rpt_type"
                        + ", @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_BGM_ProjectOperationReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@bgm_proj_oper_rpt_seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@descr", info.descr);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(3, "@bgm_trans_from_seq", "");
                dbManager.AddParameters(4, "@bgm_trans_to_seq", "");
                dbManager.AddParameters(5, "@money_req_dtls_seq", info.money_req_dtls_seq);
                dbManager.AddParameters(6, "@return_dtls_seq", info.return_dtls_seq);
                dbManager.AddParameters(7, "@petition_dtls_seq", info.petition_dtls_seq);
                dbManager.AddParameters(8, "@pay_seq", info.pay_seq);

                dbManager.AddParameters(9, "@used_amount", info.used_amount);
                dbManager.AddParameters(10, "@cal_amount", info.cal_amount);

                dbManager.AddParameters(11, "@updated_by", info.updated_by);
                dbManager.AddParameters(12, "@updated_when", info.updated_when);
                dbManager.AddParameters(13, "@receive_seq", info.receive_seq);

                strSQL = "UPDATE bgm_project_operation_report"
                    + " set descr = @descr"
                    + ", bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq"
                    //+ ", bgm_trans_from_seq = @bgm_trans_from_seq"
                    //+ ", bgm_trans_to_seq = @bgm_trans_to_seq"
                    + ", money_req_dtls_seq = @money_req_dtls_seq"
                    + ", return_dtls_seq = @return_dtls_seq"
                    + ", petition_dtls_seq = @petition_dtls_seq"
                    + ", pay_seq = @pay_seq"
                    + ", receive_seq = @receive_seq"
                    + ", used_amount = @used_amount"
                    + ", cal_amount = @cal_amount"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_rpt_seq = @bgm_proj_oper_rpt_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_BGM_ProjectOperationReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_rpt_seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_report"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_rpt_seq = @bgm_proj_oper_rpt_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateStatus(M_BGM_ProjectOperationReport info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.bgm_proj_oper_rpt_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_report"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_rpt_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
